﻿using MiniExcelLibs;
using Sunny.UI;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace PanhuaWin.工厂.仓库.报表
{
    public partial class Form热卷已出库未酸轧 : UIPage
    {
        public Form热卷已出库未酸轧()
        {
            InitializeComponent();
        }

        private void uiButton3_Click(object sender, EventArgs e)
        {
            DataTable dt = 查询原料卷();
            uiDataGridView1.DataSource = dt;
            uiDataGridView1.Refresh();
            //string sql = "";
            //sql = "select count(1) from Temp_热卷执行情况 where 状态 = '在库'";
            //DataTable dt在库 = pub.GetDataTable(pub.ConnectionString, sql);
            //sql = "select count(1) from Temp_热卷执行情况 where 状态 = '出库'";
            //DataTable dt出库 = pub.GetDataTable(pub.ConnectionString, sql);

            //uiTextBox总卷数.Text = dt.Rows.Count + "";
            //uiTextBox在库数.Text = dt在库.Rows[0][0].ToString();
            //uiTextBox出库数.Text = dt出库.Rows[0][0].ToString();
        }

        private DataTable 查询原料卷()
        {
            string gguid = Guid.NewGuid().ToString();
            string sql = @"insert into Temp_热卷执行情况(guid, 热卷号, 规格, 重量, 产地, 材质, 入库时间, 入库人员,
                        入库是否传NC, 入库传NC时间)";
            sql += "    SELECT   '" + gguid + "', dbo.Bus_StockInVolume.VolumeCodeNew 热卷号,dbo.Bus_StockInVolume.Size 规格,";
            sql += @"    dbo.Bus_StockInVolume.Weight 重量,dbo.Bus_StockInVolume.Address 产地, dbo.Bus_StockInVolume.Material 材质,
                         dbo.Bus_StockInV.CreateDate 入库单制单日期,dbo.Bus_StockInV.Creator 入库单制单人, 
                        dbo.Bus_StockInV.IsSendToNC 入库推送NC状态, dbo.Bus_StockInV.SendToNCDate 入库推送NC时间                            
                        FROM      dbo.Bus_StockInV INNER JOIN
                        dbo.Bus_StockInVolume ON dbo.Bus_StockInV.guid = dbo.Bus_StockInVolume.guid 
                        where Bus_StockInV.StockInType = '原料卷入库'
                        ";
        
            if (string.IsNullOrWhiteSpace(uiTextBox原卷号.Text.Trim()) == false)
            {
                sql += " and Bus_StockInVolume.VolumeCode like '%" + uiTextBox原卷号.Text.Trim() + "%'";
            }

            if (string.IsNullOrWhiteSpace(uiTextBox热卷号.Text.Trim()) == false)
            {
                sql += " and Bus_StockInVolume.VolumeCodeNew like '%" + uiTextBox热卷号.Text.Trim() + "%'";
            }

            if (string.IsNullOrWhiteSpace(uiTextBox规格.Text.Trim()) == false)
            {
                sql += " and Bus_StockInVolume.Size like '%" + uiTextBox规格.Text.Trim() + "%'";
            }
            sql += " order by dbo.Bus_StockInV.CreateDate; ";

            sql += @"  
                        update a set a.出库时间 = c.CreateDate ,a.出库是否传NC = b.IsSendToNC,a.出库传NC时间 = b.SendToNCDate,
                        a.出库人员 = c.Creator
                        from
                        Temp_热卷执行情况 a,
                        Bus_StockOutVolume b,
                        Bus_StockOutV c
                        where a.热卷号 = b.VolumeCodeNew and b.guid = c.guid and a.guid = '" + gguid + "';";
            sql += @"   
                        update a set a.是否已生产 = 'Y' ,a.首次次报工时间 = b.首次时间,a.首次报工人员 = c.Creator
                        from
                        Temp_热卷执行情况 a,
                        (select min(CreateDate) 首次时间 ,VolumeCodeNew from  Bus_HardOutput group by VolumeCodeNew)  b,
                        (select CreateDate 时间, VolumeCodeNew, Creator from Bus_HardOutput  )  c
                        where a.热卷号 = b.VolumeCodeNew and c.VolumeCodeNew = a.热卷号 and c.时间 = b.首次时间 and a.guid = '" + gguid + "';";
            sql += @"  
                        update a set  a.最后报工时间 = b.最后时间,a.最后报工人员 = c.Creator
                        from
                        Temp_热卷执行情况 a,
                        (select max(CreateDate) 最后时间 ,VolumeCodeNew from  Bus_HardOutput group by VolumeCodeNew)  b,
                        (select CreateDate 时间, VolumeCodeNew, Creator from Bus_HardOutput  )  c
                        where a.热卷号 = b.VolumeCodeNew and c.VolumeCodeNew = a.热卷号 and c.时间 = b.最后时间 and a.guid = '" + gguid + "';";
            //sql += @"  
            //            update a set  a.大标签打印人员 = b.最后时间,a.大标签打印时间 = c.Creator
            //            from
            //            Temp_热卷执行情况 a,
            //           (select top 1 操作时间 ,操作人员 from  Bus_LablePrint) b
            //            where b.
            //            and a.guid = '" + gguid + "';";

            sql += @"  
                        update a set a.分拆卷数 = b.卷数
                        from
                        Temp_热卷执行情况 a,
                        (select  count(VolumeCodeNew) 卷数,VolumeCodeNew from  Bus_HardOutput group by VolumeCodeNew)  b
                        where a.热卷号 = b.VolumeCodeNew and a.guid = '" + gguid + "';";
            int r = pub.ExecuteSql(pub.ConnectionString, sql);
            sql = "select * from Temp_热卷执行情况 order by 入库时间";
            DataTable dt = pub.GetDataTable(pub.ConnectionString, sql);
            dt.Columns.RemoveAt(0);
            sql = "delete Temp_热卷执行情况 where guid = '" + gguid + "'";
            pub.ExecuteSql(pub.ConnectionString, sql);
            return dt;
        }

        private void uiButton2_Click(object sender, EventArgs e)
        {
            string path;
            this.saveFileDialog1.Filter = "Excel文件 (*.XLSX)|*.XLSX";
            if (this.saveFileDialog1.ShowDialog(this) == DialogResult.OK)
            {
                path = this.saveFileDialog1.FileName;
                if (!File.Exists(path))
                {
                    MiniExcel.SaveAs(path, 查询原料卷(), true, "sheet1", ExcelType.XLSX);
                }
                else
                {
                    MessageBox.Show("文件已经存在,请重试!", "提示", MessageBoxButtons.OK);
                    return;
                }

            }
        }
    }
}
